package com.credit.dao.college;/**
 * @Classname CollegeDAO
 * @Description TODO
 * @Date 2020/11/5 17:47
 * @Created by SHTL
 */

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

/**
 * @InterfaceName CollegeDAO
 * @Description 学院信息DAO接口
 */
public interface CollegeDAO extends JpaRepository<CollegeModel,Integer>, JpaSpecificationExecutor<CollegeModel> {


    // 根据 collegeid 获取当前的 collegename
    CollegeModel findByCollegeid(Integer collegeid);

    // 根据 collegename 获取当前的 collegeid
    CollegeModel findByCollegename(String collegename);


    /**
     * 获取每个学院的学生和教师信息
     * 用于管理员数据统计页面
     * **/
    @Modifying
    @Query(value = "select c.collegename,(select COUNT(*) from student_table as s where c.collegename=s.college ) as '学生人数',(select COUNT(*) from teacher_table as t where c.collegename=t.college ) as '教师人数' from college_table as c",nativeQuery = true)
    List<Map<String,Object>> getAllNums();



    // like
    List<CollegeModel> findByCollegenameLike(String like);

    // 根据 collegeid 删除
    @Transactional
    @Modifying
    @Query(value = "delete from college_table where collegeid=:collegeid",nativeQuery = true)
    Integer deleteByCollegeid(@Param("collegeid") Integer collegeid);

}
